<!DOCTYPE html>
<html>
<head>
<title>MySQL</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
/* GitHub stylesheet for MarkdownPad (http://markdownpad.com) */
/* Author: Nicolas Hery - http://nicolashery.com */
/* Version: b13fe65ca28d2e568c6ed5d7f06581183df8f2ff */
/* Source: https://github.com/nicolahery/markdownpad-github */

/* RESET
=============================================================================*/

html, body, div, span, applet, object, iframe, h1, h2, h3, h4, h5, h6, p, blockquote, pre, a, abbr, acronym, address, big, cite, code, del, dfn, em, img, ins, kbd, q, s, samp, small, strike, strong, sub, sup, tt, var, b, u, i, center, dl, dt, dd, ol, ul, li, fieldset, form, label, legend, table, caption, tbody, tfoot, thead, tr, th, td, article, aside, canvas, details, embed, figure, figcaption, footer, header, hgroup, menu, nav, output, ruby, section, summary, time, mark, audio, video {
  margin: 0;
  padding: 0;
  border: 0;
}

/* BODY
=============================================================================*/

body {
  font-family: Helvetica, arial, freesans, clean, sans-serif;
  font-size: 14px;
  line-height: 1.6;
  color: #333;
  background-color: #fff;
  padding: 20px;
  max-width: 960px;
  margin: 0 auto;
}

body>*:first-child {
  margin-top: 0 !important;
}

body>*:last-child {
  margin-bottom: 0 !important;
}

/* BLOCKS
=============================================================================*/

p, blockquote, ul, ol, dl, table, pre {
  margin: 15px 0;
}

/* HEADERS
=============================================================================*/

h1, h2, h3, h4, h5, h6 {
  margin: 20px 0 10px;
  padding: 0;
  font-weight: bold;
  -webkit-font-smoothing: antialiased;
}

h1 tt, h1 code, h2 tt, h2 code, h3 tt, h3 code, h4 tt, h4 code, h5 tt, h5 code, h6 tt, h6 code {
  font-size: inherit;
}

h1 {
  font-size: 28px;
  color: #000;
}

h2 {
  font-size: 24px;
  border-bottom: 1px solid #ccc;
  color: #000;
}

h3 {
  font-size: 18px;
}

h4 {
  font-size: 16px;
}

h5 {
  font-size: 14px;
}

h6 {
  color: #777;
  font-size: 14px;
}

body>h2:first-child, body>h1:first-child, body>h1:first-child+h2, body>h3:first-child, body>h4:first-child, body>h5:first-child, body>h6:first-child {
  margin-top: 0;
  padding-top: 0;
}

a:first-child h1, a:first-child h2, a:first-child h3, a:first-child h4, a:first-child h5, a:first-child h6 {
  margin-top: 0;
  padding-top: 0;
}

h1+p, h2+p, h3+p, h4+p, h5+p, h6+p {
  margin-top: 10px;
}

/* LINKS
=============================================================================*/

a {
  color: #4183C4;
  text-decoration: none;
}

a:hover {
  text-decoration: underline;
}

/* LISTS
=============================================================================*/

ul, ol {
  padding-left: 30px;
}

ul li > :first-child, 
ol li > :first-child, 
ul li ul:first-of-type, 
ol li ol:first-of-type, 
ul li ol:first-of-type, 
ol li ul:first-of-type {
  margin-top: 0px;
}

ul ul, ul ol, ol ol, ol ul {
  margin-bottom: 0;
}

dl {
  padding: 0;
}

dl dt {
  font-size: 14px;
  font-weight: bold;
  font-style: italic;
  padding: 0;
  margin: 15px 0 5px;
}

dl dt:first-child {
  padding: 0;
}

dl dt>:first-child {
  margin-top: 0px;
}

dl dt>:last-child {
  margin-bottom: 0px;
}

dl dd {
  margin: 0 0 15px;
  padding: 0 15px;
}

dl dd>:first-child {
  margin-top: 0px;
}

dl dd>:last-child {
  margin-bottom: 0px;
}

/* CODE
=============================================================================*/

pre, code, tt {
  font-size: 12px;
  font-family: Consolas, "Liberation Mono", Courier, monospace;
}

code, tt {
  margin: 0 0px;
  padding: 0px 0px;
  white-space: nowrap;
  border: 1px solid #eaeaea;
  background-color: #f8f8f8;
  border-radius: 3px;
}

pre>code {
  margin: 0;
  padding: 0;
  white-space: pre;
  border: none;
  background: transparent;
}

pre {
  background-color: #f8f8f8;
  border: 1px solid #ccc;
  font-size: 13px;
  line-height: 19px;
  overflow: auto;
  padding: 6px 10px;
  border-radius: 3px;
}

pre code, pre tt {
  background-color: transparent;
  border: none;
}

kbd {
    -moz-border-bottom-colors: none;
    -moz-border-left-colors: none;
    -moz-border-right-colors: none;
    -moz-border-top-colors: none;
    background-color: #DDDDDD;
    background-image: linear-gradient(#F1F1F1, #DDDDDD);
    background-repeat: repeat-x;
    border-color: #DDDDDD #CCCCCC #CCCCCC #DDDDDD;
    border-image: none;
    border-radius: 2px 2px 2px 2px;
    border-style: solid;
    border-width: 1px;
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    line-height: 10px;
    padding: 1px 4px;
}

/* QUOTES
=============================================================================*/

blockquote {
  border-left: 4px solid #DDD;
  padding: 0 15px;
  color: #777;
}

blockquote>:first-child {
  margin-top: 0px;
}

blockquote>:last-child {
  margin-bottom: 0px;
}

/* HORIZONTAL RULES
=============================================================================*/

hr {
  clear: both;
  margin: 15px 0;
  height: 0px;
  overflow: hidden;
  border: none;
  background: transparent;
  border-bottom: 4px solid #ddd;
  padding: 0;
}

/* TABLES
=============================================================================*/

table th {
  font-weight: bold;
}

table th, table td {
  border: 1px solid #ccc;
  padding: 6px 13px;
}

table tr {
  border-top: 1px solid #ccc;
  background-color: #fff;
}

table tr:nth-child(2n) {
  background-color: #f8f8f8;
}

/* IMAGES
=============================================================================*/

img {
  max-width: 100%
}
</style>
</head>
<body>
<ul>
<li>DDL(Data Defintion Languages)语句：数据定义语言，定义了不同的数据段、数据库、表、列、索引等。常用关键字：create、drop、alter</li>
<li>DML(Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新、查询数据库记录，并检查数据完整性。常用关键字：insert、delete、update、select</li>
<li>DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。定义了数据库、表、字段、用户的访问权限和安全级别。常用关键字：grant、revoke</li>
</ul>

<h2>说明</h2>

<pre><code>db_name 数据库
t_name  数据表
[XXX]   [可不写]
</code></pre>

<h2>连接MySQL</h2>

<pre><code>mysql -u[root] -p[root]
mysql -h110.110.110.110 -uroot -proot;
</code></pre>

<h2>库相关</h2>

<pre><code>create database 库名;  (创建数据库)
create database 库名 character set utf8;  (创建数据库)

show databases;  (查看数据库)
show create databases 库名;  (查看数据库详细)

use 库名;  (进入数据库)

drop database 库名;  (删除数据库)
</code></pre>

<h2>表相关</h2>

<pre><code>创建数据表
create table 表名(name varchar(10),age int)   
create table 表名(name varchar(10),age int) engine=引擎 charset=utf8;
主键--create table 表名(id int primary key,age int);
自增--create table 表名(id int primary key auto_increment,age int);
非空--create table 表名(id int primary key auto_increment, age int not null);
注释--create table 表名(id int comment '用户的id',age int comment '用户的年龄');

    -innoDB: 默认,支持数据库的高级操作,包括事务 主 外键等
    -myisam: 只具备基本的数据存储功能
    主键      primary key 
    自增      auto_increment 
    非空      not null 
    注释      comment 
    前       first
    后       after
    去重复 distinct,将相同的查询节后去除

查询表
    show create 表名 t1;  (查看表详细)
    desc 表名;  (查看表字段)

修改表
    rename table 表名 to 新表名;  (修改表名)
    alter table 表名 engine=innodb charset=utf8;  (修改引擎,字符集)

删除表
    truncate table hero;//先删除表再创建一个一样的空表
    drop table hero;    //删除表
    delete from hero;   //删除表里面的数据  
</code></pre>

<h2>字段相关</h2>

<pre><code>添加字段
    alter table 表名 add 字段 int;
    alter table 表名 add 字段 int first;
    alter table 表名 add 字段 int after 字段;(某字段后添加)

查看字段属性
    desc 表名;

修改字段属性      
    alter table 表名 change 原字段名 新字段名 [新类型];

修改字段位置
    alter table 表名 modify 字段名 类型 [位置(first/after 字段)];
    alter table 表名 modify name int first;
    alter table 表名 modify name int after id;

删除字段
    alter table 表名 drop 字段名;
</code></pre>

<h2>DML操作语句 insert、select、update、delete</h2>

<pre><code>INSERT(添加)
    insert into 表名 values(值1,值2,值3);
    insert into 表名 values(值1,值2,值3),(id,name,age);
    insert into hero (name) values ('八戒');
    insert into hero (name) values('张三'),('李四'),('王五');

select(查询)
    select * from 表名;(全表)
    select name,age from hero;(指定字段)
    select name as '姓名' from emp;(别名)
    select ename '姓名' from emp;(别名)
    select ename 姓名,job 工作 from emp;(别名)
    select distinct job from emp;(distinct 去重)

UPDATE(更新 修改)
    update 表名 set 新值 where 条件;

DELETE(删除)
    truncate table hero;//先删除表再创建一个一样的空表
    delete from 表名 where 条件; 
    delete from 表;  //删除整个表的数据,因为没有条件
</code></pre>

<h2>where 条件</h2>

<pre><code>and 和 or
like:
    where title like '%记事本%'; 
    "%"替代一个或者多个字符  "_"仅替换一个字符 
    "a%"以a开头的字  "%a"以a结尾  %a%包含a
    "_a%" 第二个字母是a  "%a__"倒数第三个a  
比较操作:
    =,&lt;,&gt;,&lt;=,&gt;=,!=(&lt;&gt;)

两值之间 between
    where id between 10 and 100; 

多个值 in
    where id in (238,917,163);

取反 not
    where id not between 10 and 100; 
    where id ont in (238,917,163);

空 is null
    where id is null;   

非空 is not null
    where 表 is not null;
</code></pre>

<h2>order by 排序</h2>

<pre><code>需要写在where子句后面,    asc 升序(默认)  desc 降序
where title like '%dell%' order by 字段 desc;
where title like '%dell%' order by category_id desc, price;(降序-升序)
</code></pre>

<h2>group by 分组</h2>

<pre><code>统计部门人数
select deptno,count(*) from emp group by deptno;
统计男女
select sex,count(*) from emp group by sex;
</code></pre>

<h2>having 条件分组</h2>

<pre><code>having语句要写在 group by 后面,    不能在where后面对聚合函数结果进行过滤
查询每个部门的平均工资,只显示平均工资大于2000的
错误:
    select deptno,avg(sal) a from emp where a&gt;2000 group by deptno;
正确:
     select deptno,avg(sal) a from emp group by deptno having a&gt;2000;
</code></pre>

<h2>limit x,y   结果分页</h2>

<pre><code>limit 0,5  //查询第一页 每页显示5条
limit 10,5     //查询第三页 每页显示5条
limit  12,3   //第五页 每页3条
</code></pre>

<h2>子查询 嵌套查询</h2>

<pre><code>子查询: 嵌套到sql语句里面的查询sql语句称作为"子查询"
where后面作为条件, from后面作为一张新表

    查询工资最高的员工的所有信息 
    select * from emp where sal=(select max(sal) from emp);
    查询工资超过平均线的所有员工信息
    select * from emp where sal&gt;(select avg(sal) from emp);
</code></pre>

<h2>关联查询数据</h2>

<pre><code>笛卡尔积: 如果不写关联关系,查询出的两张表的乘积称为笛卡尔积

等值连接: select * from A,B where A.x=B.x and age&gt;18

内连接:select * from A [inner] join B on A.x=B.x where age&gt;18

左/右外连接: 已某一张表为主显示全部数据,另一张表只显示关联数据的查询方式为外连接
    查询结果以左边的表为主 内容全部显示 右边的表只显示有关系的
    select * from A Left outer join B on A.字段=B.字段
    select * from A Left join B on A.字段=B.字段
</code></pre>

<h2>视图</h2>

<pre><code>视图实际上就代表了一段sql查询语句,也可以理解成是一张虚拟的表,虚拟表中的数据会随着原表数据改变而改变

创建视图
    create view 视图名 as 子查询
    create view view_emp_10 as (select * from emp where deptno=10);
修改视图
    create or replace view v_emp_20 as (select * from emp where deptno=20 and sal&lt;1000);
删除视图
    drop view view_emp_10;
    drop view if exists view_emp_10;//java中使用

视图的增查改删
insert into v_emp_20 (empno,ename,deptno,sal) values(12321,'奥巴马',20,2000);
select * from 视图名;
update v_emp_20 set ename='特朗普' where empno=12321;
delete from v_emp_20 where empno=12321;
</code></pre>

<h2>存储过程</h2>

<pre><code>像C语言的函数
</code></pre>

<h2>函数</h2>

</body>
</html>
<!-- This document was created with MarkdownPad, the Markdown editor for Windows (http://markdownpad.com) -->
